--定义存储过程实现查询出账户余额最低的银行卡账户信息,显示银行卡号，姓名，账户余额
create proc proc_GetBankMessage
as
select a.RealName,b.CardNo,b.CardMoney from AccountInfo a inner join BankCard b on a.AccountId = b.AccountId
where b.CardMoney = (select min(CardMoney) from BankCard)
go
exec proc_GetBankMessage
go
--模拟银行卡存钱操作，传入银行卡号，存钱金额，实现存钱操作
create proc proc_InMoney
@cardNo varchar(20),
@InMoney money
as 
begin
	update BankCard set CardMoney += @InMoney where CardNo = @cardNo
	insert into CardExchange values(@cardNo,@InMoney,0,getdate())

end
go
exec proc_InMoney '6225547854125656',5000
go

--模拟银行卡取钱操作，传入银行卡号，取钱金额，实现取钱操作，取钱成功，返回1，取钱失败返回-1
create proc proc_OutMoney
@cardNo varchar(20),
@OutMoney money
as 
begin
	declare @code int
	declare @CardMoney money = (select CardMoney from BankCard where CardNo = @cardNo)
	if(@CardMoney - @OutMoney > 0)
		begin
			update BankCard set CardMoney -= @CardMoney where CardNo = @cardNo
			insert into CardExchange values (@cardNo,0,@OutMoney,getdate())
			return 1
		end
	return -1
end
go
declare @code int 
 exec @code = proc_OutMoney '6225547858741263',7000
 print @code
 go
 --**查询出某时间段的银行存取款信息以及存款总金额**，取款总金额，传入开始时间，结束时间，
 --显示存取款交易信息的同时，返回存款总金额，取款总金额。
 create proc proc_GetChargeMessage
 @Start_time smalldatetime,
 @End_time smalldatetime,
 @SumInMoney money output,
 @SumOutMoney money output
 as
 begin
	select @SumInMoney = sum(MoneyInBank),@SumOutMoney = sum(MoneyOutBank)  from CardExchange
	where ExchangeTime between @Start_time and @End_time
 end
 go
declare @Start_time smalldatetime = '2022-09-05 ', @End_time smalldatetime = '2022-09-19'
declare  @SumInMoney money , @SumOutMoney money 
exec proc_GetChargeMessage  @Start_time,@End_time,@SumInMoney output,@SumOutMoney output
select @SumInMoney 存款总金额,@SumOutMoney 取款总金额
go
--**密码升级**，传入用户名和密码，如果用户名密码正确，并且密码长度<8，自动升级成8位密码
--(提示：随机生成 0-9 的整数： floor(rand()*10))  rand():随机生成0.0-1.0的小数 floor:向下取整）
create proc proc_PassWordUp
@userName varchar(10),@passWord varchar(10) output 
as
begin
	if(@userName = '呼呼' and @passWord = '123456' and len(@passWord)<8)
	begin
		set @passWord = Convert(decimal(8,0),floor(rand()*100000000))
	end
end
go
drop proc proc_PassWordUp
declare @userName varchar(10) = '呼呼'
declare @passWord varchar(10) = '123456'

exec proc_PassWordUp @userName,@passWord output
select @passWord


